use crate::error::{new_err, AppErr, SqlxErr};
use crate::utils::current_timestamp;
use serde::Serialize;
use sqlx::Executor;
use sqlx::Row;

use super::get_pool;

const CREATE_SQL: &'static str = r#"
CREATE TABLE IF NOT EXISTS tb_firm (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    name TEXT NOT NULL, 
    create_timestamp INTEGER NOT NULL,
    size INTEGER NOT NULL, 
    c_type INTEGER NOT NULL, 
    description TEXT NOT NULL, 
    version TEXT NOT NULL, 
    body BLOB NOT NULL, 
    UNIQUE(c_type, version)
)
"#;

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

pub async fn download(id: i64) -> Result<(Vec<u8>, String), AppErr> {
    let row = sqlx::query(r#"SELECT name, body FROM tb_firm WHERE id = ?"#)
        .bind(id)
        .fetch_optional(get_pool())
        .await?
        .ok_or(new_err("没有找到该固件"))?;

    let body: Vec<u8> = row.get(0);
    let name: String = row.get(1);

    Ok((body, name))
}

pub async fn create(
    name: &str,
    c_type: u8,
    description: &str,
    version: &str,
    body: &[u8],
) -> Result<i64, AppErr> {
    let ret = sqlx::query(r#"
        INSERT INTO tb_firm (name, create_timestamp, size, c_type, description, version, body) VALUES (?, ?, ?, ?, ?, ?, ?)
    "#)
    .bind(name)
    .bind( current_timestamp() as i64 )
    .bind(body.len() as i64)
    .bind(c_type)
    .bind(description)
    .bind(version)
    .bind(body)
    .execute(get_pool())
    .await?;

    Ok(ret.last_insert_rowid())
}

#[derive(Debug, Serialize)]
pub struct Firm {
    name: String,
    size: i64,
    description: String,
    version: String,
    create_timestamp: i64,
}

pub async fn query(c_type: u8) -> Result<Box<[Firm]>, AppErr> {
    let rows = sqlx::query(
        r#"
        SELECT name, create_timestamp,  size, description, version FROM tb_firm WHERE c_type = ?
    "#,
    )
    .bind(c_type)
    .fetch_all(get_pool())
    .await?;
    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let firm = Firm {
            name: row.get(0),
            create_timestamp: row.get(1),
            size: row.get(2),
            description: row.get(3),
            version: row.get(4),
        };
        vec.push(firm);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn del(id: i64) -> Result<(), AppErr> {
    sqlx::query(
        r#"
        DELETE FROM tb_firm WHERE id = ?
    "#,
    )
    .bind(id)
    .execute(get_pool())
    .await?;

    Ok(())
}
